﻿#region Librerias
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
#endregion

/// <summary>
/// Class ServiceProfile, dependiente de la clase Person, define una persona natural
/// </summary>
//tratamiento especial para AGE
#region Class

public class clsServiceProfile : DAC {
    #region Variables

    private int idServiceProfile, idClient, idService, idYear, idMode, iCompleted;
    private String sMonth, dtDate, sNameClient, sNameService;
    private const String tabla = "dbo.tblServiceProfile";

    #endregion

    #region Builder

    /// <summary>
    /// Constructor
    /// </summary>
    public clsServiceProfile() { }

    #endregion

    #region Methods

    #region Consultations

    public DataSet SeeAllTheRecords() {
        String query = "SELECT row_number() over(order by " + tabla + ".idServiceProfile) As NUMBER, ";
        query += tabla + ".idServiceProfile As ID, " + tabla + ".idService As SERVICE, ";
        query += tabla + ".idClient As CLIENT, dbo.tblYear.sName As YEAR, ";
        query +=         "dbo.tblMode.sName As MODE, " + tabla + ".dtDate As DATE, ";
        query += tabla + ".sMonth As MONTH, " + tabla + ".iCompleted As COMPLETE, ";
        query += tabla + ".sNameClient As NAMECLIENT, " + tabla + ".sNameService As NAMESERVICE ";
        query += "FROM " + tabla + ", dbo.tblYear, dbo.tblMode ";
        query += "WHERE " + tabla + ".idYear=dbo.tblYear.idYear ";
        query += "OR " + tabla + ".idMode=dbo.tblMode.idMode ";

        return this.ExecuteQuery(query);
    }

    public DataSet SeeAllTheRecords(int startRecord, int maxRecord, String  txtIdClient, String txtIdService, String txtIdYear){
        String query = "SELECT row_number() over(order by " + tabla + ".idServiceProfile) As NUMBER, ";
        query += tabla + ".idServiceProfile As ID, " + tabla + ".idService As SERVICE, ";
        query += tabla + ".idClient As CLIENT, dbo.tblYear.sName As YEAR, ";
        query +=         "dbo.tblMode.sName As MODE, " + tabla + ".dtDate As DATE, ";
        query += tabla + ".sMonth As MONTH, " + tabla + ".iCompleted As COMPLETE, ";
        query += tabla + ".sNameClient As NAMECLIENT, " + tabla + ".sNameService As NAMESERVICE ";
        query += "FROM " + tabla + ", dbo.tblYear, dbo.tblMode, dbo.tblService ";
        query += "WHERE " + tabla + ".idClient =" + txtIdClient + " ";
        query += "AND " + tabla + ".idMode = " + "dbo.tblMode.idMode ";
        query += "AND " + tabla + ".idYear = " + "dbo.tblYear.idYear ";
        query += "AND " + tabla + ".idService = " + "dbo.tblService.idService ";
        if(!txtIdService.Equals("0")){            
            query += "AND dbo.tblService.idService =" + txtIdService + " ";
        }
        if(!txtIdYear.Equals("0") || !txtIdYear.Equals("0")){            
            query += "AND dbo.tblYear.idYear =" + txtIdYear + " ";
        }
        if (startRecord == 0 && maxRecord == 0) {
            return this.ExecuteQuery(query);
        }
        else {
            return this.ExecuteQuery(query, startRecord, maxRecord);
        }        
    }

    public DataSet ReportClientForTypeService(int startRecord, int maxRecord, String txtIdService){
        String query = "SELECT row_number() over(order by aux.CLIENT) As NUMBER, aux.CLIENT As CLIENT, aux.SERVICE As SERVICE "; 
               query += "FROM (select distinct dbo.tblServiceProfile.sNameClient As CLIENT, dbo.tblServiceProfile.sNameService As SERVICE from dbo.tblServiceProfile ";
               if(!txtIdService.Equals("0")){
                   query += "where " + tabla + ".idService =" + txtIdService + " ";
               }
               query += ") aux";
        
        if (startRecord == 0 && maxRecord == 0) {
            return this.ExecuteQuery(query);
        }
        else {
            return this.ExecuteQuery(query, startRecord, maxRecord);
        }        
    }

    public DataSet Report(String txtIdService){
        String query = "SELECT row_number() over(order by aux.CLIENT) As NUMBER, aux.CLIENT As CLIENT, aux.SERVICE As SERVICE "; 
               query += "FROM (select distinct dbo.tblServiceProfile.sNameClient As CLIENT, dbo.tblServiceProfile.sNameService As SERVICE from dbo.tblServiceProfile ";
               if(!txtIdService.Equals("0")){
                   query += "where " + tabla + ".idService =" + txtIdService + " ";
               }
               query += ") aux";
        
        
            return this.ExecuteQuery(query);
        
    }

    public DataSet ConsultID(String id) {
        String query = "SELECT ";
        query += tabla + ".idServiceProfile As ID, " + tabla + ".idService As SERVICE, ";
        query += tabla + ".idClient As CLIENT, " + tabla + ".idYear As YEAR, ";
        query += tabla + ".idMode As MODE, " + tabla + ".dtDate As DATE, ";
        query += tabla + ".sMonth As MONTH, " + tabla + ".iCompleted As COMPLETE, ";
        query += tabla + ".sNameClient As NAMECLIENT, " + tabla + ".sNameService As NAMESERVICE ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idServiceProfile =" + id;
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultModeID(String id) {
        String query = "SELECT ";
        query += tabla + ".idServiceProfile As ID, " + tabla + ".idService As SERVICE, ";
        query += tabla + ".idClient As CLIENT, " + tabla + ".idYear As YEAR, ";
        query += tabla + ".idMode As MODE, " + tabla + ".dtDate As DATE, ";
        query += tabla + ".sMonth As MONTH, " + tabla + ".iCompleted As COMPLETE, ";
        query += tabla + ".sNameClient As NAMECLIENT, " + tabla + ".sNameService As NAMESERVICE ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idMode =" + id;
        return this.ExecuteQuery(query);
    }
    public DataSet ConsultServiceID(String id) {
        String query = "SELECT ";
        query += tabla + ".idServiceProfile As ID, " + tabla + ".idService As SERVICE, ";
        query += tabla + ".idClient As CLIENT, " + tabla + ".idYear As YEAR, ";
        query += tabla + ".idMode As MODE, " + tabla + ".dtDate As DATE, ";
        query += tabla + ".sMonth As MONTH, " + tabla + ".iCompleted As COMPLETE, ";
        query += tabla + ".sNameClient As NAMECLIENT, " + tabla + ".sNameService As NAMESERVICE ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idService =" + id;
        return this.ExecuteQuery(query);
    }
    public DataSet ConsultClientID(String id) {
        String query = "SELECT ";
        query += tabla + ".idServiceProfile As ID, " + tabla + ".idService As SERVICE, ";
        query += tabla + ".idClient As CLIENT, " + tabla + ".idYear As YEAR, ";
        query += tabla + ".idMode As MODE, " + tabla + ".dtDate As DATE, ";
        query += tabla + ".sMonth As MONTH, " + tabla + ".iCompleted As COMPLETE, ";
        query += tabla + ".sNameClient As NAMECLIENT, " + tabla + ".sNameService As NAMESERVICE ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idClient =" + id;
        return this.ExecuteQuery(query);
    }
    public DataSet ConsultYearID(String id) {
        String query = "SELECT ";
        query += tabla + ".idServiceProfile As ID, " + tabla + ".idService As SERVICE, ";
        query += tabla + ".idClient As CLIENT, " + tabla + ".idYear As YEAR, ";
        query += tabla + ".idMode As MODE, " + tabla + ".dtDate As DATE, ";
        query += tabla + ".sMonth As MONTH, " + tabla + ".iCompleted As COMPLETE, ";
        query += tabla + ".sNameClient As NAMECLIENT, " + tabla + ".sNameService As NAMESERVICE ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idYear =" + id;
        return this.ExecuteQuery(query);
    }

    #endregion

    public int Insert() {
        String query = "INSERT INTO " + tabla;
        query += "(idService, idClient, idYear, idMode, dtDate, sMonth, sNameClient, sNameService, iCompleted) ";
        query += "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}') SELECT CAST(scope_identity() AS int)";

        String sql = String.Format(query, this.IdService, this.IdClient, this.IdYear, this.IdMode, this.DtDate, this.SMonth, this.SNameClient, this.SNameService, this.ICompleted);
        return this.ExecuteDML(sql, "no");
    }

    public int Update(){
        String query = "UPDATE " + tabla + " SET ";        
        query += tabla + ".idService=" + this.IdService + ", " + tabla + ".idClient=" + this.IdClient + ", ";
        query += tabla + ".idYear=" + this.IdYear + ", " + tabla + ".idMode=" + this.IdMode + ", ";
        query += tabla + ".dtDate='" + this.DtDate + "', " + tabla + ".sMonth='" + this.SMonth + "', " + tabla + ".iCompleted=" + this.ICompleted + ", ";
        query += tabla + ".sNameClient='" + this.SNameClient + "', " + tabla + ".snameService='" + this.SNameService + "' ";
        query += "WHERE " + tabla + ".idServiceProfile=" + this.ID;
        return this.ExecuteDML(query, "no");
    }

    public int Delete()
    {
        String query = "DELETE FROM " + tabla + " WHERE " + tabla + ".idServiceProfile={0}";
        String sql = String.Format(query, this.ID);
        return this.ExecuteDML(sql, "no");
    }

    #endregion

    #region Setters and Getters

    
    public int ID
    {
        get { return idServiceProfile; }
        set { idServiceProfile = value; }
    }

    public int IdClient {
        get { return idClient; }
        set { idClient = value; }
    }
    
    public String SMonth
    {
        get { return sMonth; }
        set { sMonth = value; }
    }
    
    public String DtDate {
        get { return dtDate; }
        set { dtDate = value; }
    }       
    public int IdYear {
        get { return idYear; }
        set { idYear = value; }
    }

    public int IdService {
        get { return idService; }
        set { idService = value; }
    }
    public int IdMode {
        get { return idMode; }
        set { idMode = value; }
    }
    public String SNameService {
        get { return sNameService; }
        set { sNameService = value; }
    }

    public String SNameClient {
        get { return sNameClient; }
        set { sNameClient = value; }
    }

    public int ICompleted {
        get { return iCompleted; }
        set { iCompleted = value; }
    }
    #endregion

}

#endregion